﻿using System;
using System.Data;
using System.Text;
using System.Globalization;
using System.Collections;

using BizElements.Core.ProviderComponents;

namespace BizElements.Core.SqlServer
{
    sealed class SqlServerRecursiveCteExecutor : IHierarchicalQueryExecutor
    {
        [Serializable]
        private class HierarchyTable : DbTable
        {
            #region Meta.

            public HierarchyTable(IDbTable targetTable, bool superriors)
                : base(CreateTableName(targetTable), CreateAlias(targetTable, superriors), CreateColumns(targetTable), CreatePkColNames(targetTable), false)
            {
                this.catalog = targetTable.Catalog;
            }

            const string defaultTableName = "hierarchy";
            const string uniqueTableName = "hierarchy_D8CE1568EF4D439091DB4F188EB169A9";

            private static string CreateTableName(IDbTable targetTable)
            {
                bool shouldUseUniqueName = (targetTable.TableName.ToLowerInvariant() == defaultTableName) || (targetTable.Alias.ToLowerInvariant() == defaultTableName);
                return (shouldUseUniqueName) ? uniqueTableName : defaultTableName;
            }

            const string defaultSuperriorsAlias = "superriors";
            const string uniqueSuperriorsAlias = "superriors_D8CE1568EF4D439091DB4F188EB169A9";

            private static string CreateAlias(IDbTable targetTable, bool superriors)
            {
                if (superriors)
                {
                    bool shouldUseUniqueName = (targetTable.TableName.ToLowerInvariant() == defaultTableName) || (targetTable.Alias.ToLowerInvariant() == defaultTableName);
                    return (shouldUseUniqueName) ? uniqueSuperriorsAlias : defaultSuperriorsAlias;
                }
                else
                {
                    return null;
                }
            }

            const string defaultLevelColumnName = "level";
            const string uniqueLevelColumnName = "level_D8CE1568EF4D439091DB4F188EB169A9";

            private static string CreateLevelColumnName(IDbTable targetTable)
            {
                bool shouldUseUniqueName = false;
                foreach (IDbColumn col in targetTable.Columns)
                {
                    if (col.ColumnName.ToLowerInvariant() == defaultLevelColumnName)
                    {
                        shouldUseUniqueName = true;
                        break;
                    }

                    if (col.Alias.ToLowerInvariant() == defaultLevelColumnName)
                    {
                        shouldUseUniqueName = true;
                        break;
                    }
                }

                return (shouldUseUniqueName) ? uniqueLevelColumnName : defaultLevelColumnName;
            }

            private static string[] CreatePkColNames(IDbTable targetTable)
            {
                string[] pkColNames = new string[targetTable.PrimaryKey.Count];
                for (int pkPart = 0; pkPart < pkColNames.Length; pkPart++)
                    pkColNames[pkPart] = targetTable.PrimaryKey[pkPart].ColumnName;

                return pkColNames;
            }

            private static DbColumnCollection CreateColumns(IDbTable targetTable)
            {
                DbColumnCollection levelAndPk = new DbColumnCollection();
                levelAndPk.Add(new DbColumn(CreateLevelColumnName(targetTable), null, null, DbType.Int32, typeof(int), false, 0,
                    false, null, 0, false, false, false, "Level", int.MinValue, int.MaxValue, false, null));

                int ordinal = 1;
                foreach (IDbColumn pkPart in targetTable.PrimaryKey)
                {
                    levelAndPk.Add(new DbColumn(pkPart.ColumnName, null, null, pkPart.DbType, pkPart.DataType, pkPart.IsNullable, ordinal,
                        pkPart.AutoIncrement, pkPart.DefaultValue, pkPart.MaxLengthIfText, pkPart.IsPrimaryKeyPart, pkPart.IsForeignKeyPart,
                        pkPart.IsUniqueConstraintPart, pkPart.PropertyName, pkPart.MinValue, pkPart.MaxValue, pkPart.IsAutoGenerated, pkPart.SequenceName));
                    ordinal++;
                }
                return levelAndPk;
            }

            public IDbColumn Level
            {
                get { return this.Columns[0]; }
            }

            protected override void InitForeignKeys()
            {
            }

            ICatalog catalog;

            public override ICatalog Catalog
            {
                get { return this.catalog; }
            }

            public override IDbTable Clone(string cloneAlias)
            {
                throw new NotImplementedException();
            }

            public override IEntity NewEntity()
            {
                throw new NotImplementedException();
            }

            public override IObjectReader NewEntityReader()
            {
                throw new NotImplementedException();
            }

            #endregion
        }

        [Serializable]
        private class RecursiveStatement : SqlStatementBase
        {
            #region CTor.

            public RecursiveStatement(IEntity rootEntity, DbRelation recursiveRelation)
                : base(rootEntity.Table)
            {
                this.rootEntity = rootEntity;
                this.relation = recursiveRelation;
            }

            #endregion

            #region Properties.

            IEntity rootEntity;

            private IEntity RootEntity
            {
                get { return this.rootEntity; }
            }

            DbRelation relation;

            private DbRelation Relation
            {
                get { return this.relation; }
            }

            [NonSerialized]
            HierarchyTable hierarchy;

            public HierarchyTable Hierarchy
            {
                get
                {
                    if (this.hierarchy == null)
                        this.hierarchy = new HierarchyTable(TargetTable, false);

                    return this.hierarchy;
                }
            }

            [NonSerialized]
            HierarchyTable superriors;

            private HierarchyTable Superriors
            {
                get
                {
                    if (this.superriors == null)
                        this.superriors = new HierarchyTable(TargetTable, true);

                    return this.superriors;
                }
            }

            SelectStatement selectTree;

            public SelectStatement Select
            {
                get { return this.selectTree; }
                set { this.selectTree = value; }
            }

            UpdateStatement updateTree;

            public UpdateStatement Update
            {
                get { return this.updateTree; }
                set { this.updateTree = value; }
            }

            #endregion

            #region RenderUpdate.

            public override void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
            {
                SqlStatementBase cteConsumer;
                if (this.Select != null)
                    cteConsumer = this.Select;
                else if (this.Update != null)
                    cteConsumer = this.Update;
                else
                    throw new InvalidOperationException(Messages.RecursiveCteExecutor_StatementWichUsesCteIsNotYetSet);

                RenderWithDeclaration(dbms, output);
                output.Append("( ");
                SelectStatement anchorMember = CreateAnchorMember();
                anchorMember.Render(dbms, output, parameters);
                output.Append(" UNION ALL ");
                SelectStatement recursiveMember = CreateRecursiveMember();
                recursiveMember.Render(dbms, output, parameters);
                output.Append(" ) ");

                cteConsumer.Render(dbms, output, parameters);
            }

            private void RenderWithDeclaration(DbmsType dbms, StringBuilder output)
            {
                output.Append("WITH ");
                this.Hierarchy.RenderTableName(dbms, output);
                output.Append("(");
                for (int idxCol = 0; idxCol < this.Hierarchy.Columns.Count; idxCol++)
                {
                    this.Hierarchy.Columns[idxCol].RenderColumnName(dbms, output);
                    bool isLastCol = (idxCol == this.Hierarchy.Columns.Count - 1);
                    if (isLastCol)
                        output.Append(") AS ");
                    else
                        output.Append(", ");
                }
            }

            const string zeroAsLevel = "0 AS {0}";

            private SelectStatement CreateAnchorMember()
            {
                SelectStatement select = new SelectStatement(TargetTable);
                select.SelectList.Add(new SelectItem(string.Format(CultureInfo.InvariantCulture, zeroAsLevel, this.Hierarchy.Level.ColumnName)));
                foreach (IDbColumn pkField in TargetTable.PrimaryKey)
                    select.SelectList.Add(pkField);

                foreach (IDbColumn pkField in TargetTable.PrimaryKey)
                    select.Where.Add(pkField, this.RootEntity.GetField(pkField));

                return select;
            }

            const string levelPlusOneAsLevel = "({0} + 1) AS {1}";

            private SelectStatement CreateRecursiveMember()
            {
                SelectStatement select = new SelectStatement(TargetTable);
                select.SelectList.Add(new SelectItem(string.Format(CultureInfo.InvariantCulture, levelPlusOneAsLevel, this.Hierarchy.Level.ColumnName, this.Hierarchy.Level.ColumnName)));
                foreach (IDbColumn pkField in TargetTable.PrimaryKey)
                    select.SelectList.Add(pkField);

                DbRelation joinTargetToSupperriors = new DbRelation(this.Superriors, GetSuperriorsPK(), TargetTable, GetTargetFK(), null);
                select.Relations.Add(joinTargetToSupperriors, false, false);
                return select;
            }

            #endregion

            #region Execute.

            public DataTable ExecuteSelect()
            {
                if (this.Select == null)
                    throw new InvalidOperationException(Messages.RecursiveCteExecutor_StatementWichUsesCteIsNotYetSet);

                DbParameterCollection parameters = new DbParameterCollection();
                StringBuilder cmdText = new StringBuilder();
                Render(this.ConnectionProvider.DBMS, cmdText, parameters);

                string command = cmdText.ToString();
                this.LastExecutedCommandInfo = new CommandExecutionStatistics(command);
                DataTable data = DbUtil.ExecuteQuery(this.ConnectionProvider, command, parameters, CommandType.Text, TargetTable.Alias);
                this.LastExecutedCommandInfo.StopTime();
                return data;
            }

            public DataTable ExecuteSelect(IConnectionProvider conn)
            {
                this.ConnectionProvider = conn;
                return ExecuteSelect();
            }

            public int ExecuteUpdate()
            {
                if (this.Update == null)
                    throw new InvalidOperationException(Messages.RecursiveCteExecutor_UpdateStatementWichUsesCteIsNotYetSet);

                DbParameterCollection parameters = new DbParameterCollection();
                StringBuilder cmdText = new StringBuilder();
                Render(this.ConnectionProvider.DBMS, cmdText, parameters);

                string command = cmdText.ToString();
                this.LastExecutedCommandInfo = new CommandExecutionStatistics(command);
                int rowsAffected = DbUtil.ExecuteNonQuery(this.ConnectionProvider, command, parameters, CommandType.Text);
                this.LastExecutedCommandInfo.StopTime();
                return rowsAffected;
            }

            public int ExecuteUpdate(IConnectionProvider conn)
            {
                this.ConnectionProvider = conn;
                return ExecuteUpdate();
            }

            #endregion

            #region Keys.

            private IDbColumn[] GetSuperriorsPK()
            {
                IDbColumn[] pkSuperriors = new IDbColumn[this.Relation.ParentPrimaryKey.Length];
                for (int pkPart = 0; pkPart < pkSuperriors.Length; pkPart++)
                {
                    string colName = this.Relation.ParentPrimaryKey[pkPart].ColumnName;
                    pkSuperriors[pkPart] = this.Superriors.Columns.GetByColumnName(colName);
                }

                return pkSuperriors;
            }

            private IDbColumn[] GetTargetFK()
            {
                IDbColumn[] fkTarget = new IDbColumn[this.Relation.ChildForeignKey.Length];
                for (int fkPart = 0; fkPart < fkTarget.Length; fkPart++)
                {
                    string colName = this.Relation.ChildForeignKey[fkPart].ColumnName;
                    fkTarget[fkPart] = TargetTable.Columns.GetByColumnName(colName);
                }

                return fkTarget;
            }

            public IDbColumn[] GetHierarchyPK()
            {
                IDbColumn[] pkHierarchy = new IDbColumn[this.Relation.ParentPrimaryKey.Length];
                for (int pkPart = 0; pkPart < pkHierarchy.Length; pkPart++)
                {
                    string colName = this.Relation.ParentPrimaryKey[pkPart].ColumnName;
                    pkHierarchy[pkPart] = this.Hierarchy.Columns.GetByColumnName(colName);
                }

                return pkHierarchy;
            }

            #endregion
        }

        #region Common - private static methods.

        private static SearchCondition CreateWhere(IEntity rootEntity, RecursiveStatement cte, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            // where exists (select hierarchy.id from hierarchy where hierarchy.id = target.id and level betwen beginAtLevel and endAtLevel)
            // and status = ...
            SelectStatement existsInHierarchy = new SelectStatement(cte.Hierarchy, cte.GetHierarchyPK());
            foreach (IDbColumn targetPkField in rootEntity.Table.PrimaryKey)
            {
                IDbColumn hierarchyPkField = cte.Hierarchy.Columns.GetByColumnName(targetPkField.ColumnName);
                existsInHierarchy.Where.Add(PredicateFactory.Compare(hierarchyPkField, "=", targetPkField));
            }

            bool hasLevelConstraints = (beginAtLevel > 0) || (endAtLevel < int.MaxValue);
            if (hasLevelConstraints)
                existsInHierarchy.Where.Add(PredicateFactory.Between(cte.Hierarchy.Level, beginAtLevel, endAtLevel));

            SearchCondition where = (SearchCondition)PredicateFactory.Exists(existsInHierarchy);
            if (leafFilter != null && !leafFilter.IsEmpty)
                where.Add(leafFilter);

            return where;
        }

        private static void EnsureRecursiveRelation(DbRelation recursiveRelation)
        {
            bool isRecursive = (recursiveRelation.Parent.TableName == recursiveRelation.Child.TableName);
            if (!isRecursive)
                throw new ArgumentException(Messages.RecursiveCteExecutor_RecursiveRelationIsRequired, "recursiveRelation");
        }

        private static void EnsureValidBeginAndEndLevels(int beginAtLevel, int endAtLevel)
        {
            if (beginAtLevel < 0) 
                throw new ArgumentException(Messages.RecursiveCteExecutor_MustBePositiveInteger, "beginAtLevel");
            if (endAtLevel < 0)
                throw new ArgumentException(Messages.RecursiveCteExecutor_MustBePositiveInteger, "beginAtLevel");
            if (endAtLevel < beginAtLevel)
                throw new ArgumentException(Messages.RecursiveCteExecutor_EndLevelMustBeGreaterThanBeginLevel);
        }

        #endregion

        #region SelectTree.

        public DataTable SelectTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, OrderByClause sorter, bool sortOnDb, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            // select target.* from target where exists ... and ...
            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(rootEntity.Table, rootEntity.Table.Columns);
            cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
            if (sortOnDb && !OrderByClause.IsNullOrEmpty(sorter))
                cte.Select.OrderBy = sorter;

            DataTable data = cte.ExecuteSelect(conn);
            if (!sortOnDb && !OrderByClause.IsNullOrEmpty(sorter))
                data = GenericHierarchicalQueryExecutor.SortDataTable(data, sorter);

            return data;
        }

        #endregion

        #region DetermineTreeDepth.

        public int DetermineTreeDepth(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation)
        {
            EnsureRecursiveRelation(recursiveRelation);

            // select max(level) as maxLevelIndex from hierarchy
            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(cte.Hierarchy, AggregateFunctionFactory.Max(cte.Hierarchy.Level, null));

            DataTable results = cte.ExecuteSelect(conn);
            int highestLevel = (results.Rows[0][0] != DBNull.Value) ? Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture) : 0;
            int depth = highestLevel + 1;

            return depth;
        }

        #endregion

        #region UpdateTree.

        public int UpdateTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            // update target set ... where exists ... and ...
            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Update = new UpdateStatement(rootEntity.Table);
            cte.Update.UpdateList = setExpressions;
            cte.Update.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);

            int rowsAffected = cte.ExecuteUpdate(conn);
            return rowsAffected;
        }

        #endregion

        #region Aggregates

        public int CountTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Count(null));
            cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
            DataTable result = cte.ExecuteSelect(conn);
            int count = Convert.ToInt32(result.Rows[0][0], CultureInfo.InvariantCulture);

            return count;
        }

        public object SumTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Sum(column, null));
            cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
            DataTable result = cte.ExecuteSelect(conn);
            return result.Rows[0][0];
        }

        public object MinInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Min(column, null));
            cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
            DataTable result = cte.ExecuteSelect(conn);
            return result.Rows[0][0];
        }

        public object MaxInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Max(column, null));
            cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
            DataTable result = cte.ExecuteSelect(conn);
            return result.Rows[0][0];
        }

        #endregion
    }
}